import models

class Student(models.Model):
    # 声明表名
    __tablename__ = "student"
    id = models.Column(models.Integer, primary_key=True, )
    name = models.Column(models.String(255))
    sex = models.Column(models.Boolean)
    age = models.Column(models.SmallInteger)
    class_name = models.Column("class", models.String(255), )
    description = models.Column(models.Text)

    def __repr__(self):
        return f"<{self.name} {self.__class__.__name__}>"


if __name__ == '__main__':
    # 如果没有提前声明模型中的数据表，则可以采用以下代码生成数据库表
    # 如果数据库中已经声明了有数据库表，则不会继续生成
    models.Model.metadata.create_all(models.engine)


    """查询student表中的所有数据"""
    student_list = models.session.query(Student).all()
    print(student_list)
    for student in student_list:
        print(student, student.id, student.name)

    """按照主键id进行查询"""
    student = models.session.get(Student, 105)
    if student:
        print(student, student.name, student.class_name)
    else:
        print("查无此人!")

    """按照条件查询"""
    data_list = models.session.query(Student).filter(Student.sex == True, Student.class_name == '301').all()
    print(data_list)
    for data in data_list:
        print(data.sex, data.name)

    """添加一条数据"""
    s1 = Student(
        name="xiaozhang",
        sex=True,
        age=17,
        class_name="305",
        description="oh oh oh oh!!!!"
    )
    models.session.add(s1)
    models.session.commit()

    """修改操作"""
    student = models.session.query(Student).filter_by(name="xiaozhang").first()
    if student:
        student.name = "xiaozhang2"
        student.age = 30
        models.session.commit()

    """删除操作"""
    models.session.query(Student).filter_by(name="xiaozhang2").delete()
    models.session.commit()